package com.oa.ylms.apps.dao.leave.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.oa.ylms.apps.constants.leave.ILeaveInfoStates;
import com.oa.ylms.apps.dao.leave.ILeaveInfoDao;
import com.oa.ylms.apps.entity.archives.ArchiveInfoValue;
import com.oa.ylms.apps.entity.archives.StaffInfoValue;
import com.oa.ylms.apps.entity.leave.LeaveInfoValue;

@SuppressWarnings("unchecked")
public class LeaveInfoDaoImpl extends HibernateDaoSupport implements
		ILeaveInfoDao {

	private List<LeaveInfoValue> list;
	private int numbers;

	public List<LeaveInfoValue> queryLeaveInfoByStaffIdWithPage(
			final StaffInfoValue staffInfo, final int rows, final int pageNo)
			throws Exception {
		final String hql = "select liv from LeaveInfoValue liv where liv.staffInfo.staffId = ? and liv.states = ?";
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				query.setInteger(0, staffInfo.getStaffId());
				query.setInteger(1, staffInfo.getArchiveInfo().getPostInfo()
						.getSectionInfo().getSectionId());
				query.setFirstResult(rows * (pageNo - 1));
				query.setMaxResults(rows);
				list = query.list();
				return list;
			}
		});
		return list;
	}

	public List<LeaveInfoValue> queryLeaveInfoByChairmanWithPage(
			final ArchiveInfoValue archiveInfo, final LeaveInfoValue leaveInfo,
			final int rows, final int pageNo) throws Exception {
		String hqlStr = "SELECT liv FROM LeaveInfoValue liv,StaffInfoValue siv,ArchiveInfoValue aiv where liv.staffInfo.staffId= siv.staffId AND siv.archiveInfo.archiveId = aiv.archiveId ";
		if (archiveInfo.getStaffName() != null) {
			hqlStr += " AND aiv.staffName =? ";
		}
		if (leaveInfo.getTitle() != null) {
			hqlStr += " AND liv.title = ?";
		}
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = -1;
				if (archiveInfo.getStaffName() != null) {
					index++;
					query.setString(index, archiveInfo.getStaffName());
				}
				if (leaveInfo.getTitle() != null) {
					index++;
					query.setString(index, leaveInfo.getTitle());
				}
				query.setFirstResult(rows * (pageNo - 1));
				query.setMaxResults(rows);
				list = query.list();
				return list;
			}
		});
		return list;
	}

	public List<LeaveInfoValue> queryLeaveInfoByMinisterWithPage(
			final int sectionId, final ArchiveInfoValue archiveInfo,
			final LeaveInfoValue leaveInfo, final int rows, final int pageNo)
			throws Exception {
		String hqlStr = "select liv from LeaveInfoValue liv, StaffInfoValue siv,ArchiveInfoValue aiv,PostInfoValue piv where liv.staffInfo.staffId = siv.staffId and siv.archiveInfo.archiveId = aiv.archiveId and aiv.postInfo.postId = piv.postId and piv.sectionInfo.sectionId =?";
		if (archiveInfo.getStaffName() != null) {
			hqlStr += " AND aiv.staffName =? ";
		}
		if (leaveInfo.getTitle() != null) {
			hqlStr += " AND liv.title = ?";
		}
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = 0;
				query.setInteger(index, sectionId);
				if (archiveInfo.getStaffName() != null) {
					index++;
					query.setString(index, archiveInfo.getStaffName());
				}
				if (leaveInfo.getTitle() != null) {
					index++;
					query.setString(index, leaveInfo.getTitle());
				}
				query.setFirstResult(rows * (pageNo - 1));
				query.setMaxResults(rows);
				list = query.list();
				return list;
			}
		});
		return list;
	}

	public int addLeaveInfo(LeaveInfoValue leaveInfo) throws Exception {
		if (leaveInfo != null) {
			this.getHibernateTemplate().save(leaveInfo);
		}
		return leaveInfo.getLeaveId();
	}

	public boolean deleteLeaveInfo(LeaveInfoValue leaveInfo) throws Exception {
		if (leaveInfo != null) {
			try {
				this.getHibernateTemplate().delete(leaveInfo);
			} catch (Exception e) {
				return false;
			}
			return true;
		}
		return false;
	}

	public boolean modifyLeaveInfo(LeaveInfoValue leaveInfo) throws Exception {
		if (leaveInfo != null) {
			try {
				this.getHibernateTemplate().update(leaveInfo);
			} catch (Exception e) {
				return false;
			}
			return true;
		}
		return false;
	}

	public LeaveInfoValue queryLeaveInfoByLeaveId(int leaveId) throws Exception {

		return (LeaveInfoValue) this.getHibernateTemplate().get(
				LeaveInfoValue.class, leaveId);
	}

	public int countTpages(final StaffInfoValue staffINfo, final int rows)
			throws Exception {
		int pageNumbers = 0;
		final String hql = "select liv from LeaveInfoValue liv where liv.staffInfo.staffId = ? and liv.states <> ?";
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				query.setInteger(0, staffINfo.getStaffId());
				query.setInteger(1, staffINfo.getArchiveInfo().getPostInfo()
						.getSectionInfo().getSectionId());
				list = query.list();
				return list;
			}
		});
		this.numbers = list.size();
		if (numbers <= rows) {
			pageNumbers = 1;
		}
		if (numbers == 0) {
			pageNumbers = 0;
		} else {
			if (numbers % rows == 0) {
				pageNumbers = numbers / rows;
			} else {
				pageNumbers = numbers / rows + 1;
			}
		}
		return pageNumbers;
	}

	public int countTpagesByMinister(final int sectionId,
			final ArchiveInfoValue archiveInfo, final LeaveInfoValue leaveInfo,
			int rows) throws Exception {
		int pageNumbers = 0;
		String hqlStr = "select liv from LeaveInfoValue liv, StaffInfoValue siv,ArchiveInfoValue aiv,PostInfoValue piv where liv.staffInfo.staffId = siv.staffId and siv.archiveInfo.archiveId = aiv.archiveId and aiv.postInfo.postId = piv.postId and piv.sectionInfo.sectionId =?";
		if (archiveInfo.getStaffName() != null) {
			hqlStr += " AND aiv.staffName =? ";
		}
		if (leaveInfo.getTitle() != null) {
			hqlStr += " AND liv.title = ?";
		}
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = 0;
				query.setInteger(index, sectionId);
				if (archiveInfo.getStaffName() != null) {
					index++;
					query.setString(index, archiveInfo.getStaffName());
				}
				if (leaveInfo.getTitle() != null) {
					index++;
					query.setString(index, leaveInfo.getTitle());
				}
				list = query.list();
				return list;
			}
		});
		this.numbers = list.size();
		if (numbers <= rows) {
			pageNumbers = 1;
		}
		if (numbers == 0) {
			pageNumbers = 0;
		} else {
			if (numbers % rows == 0) {
				pageNumbers = numbers / rows;
			} else {
				pageNumbers = numbers / rows + 1;
			}
		}
		return pageNumbers;
	}

	public int countTpagesByChairman(final ArchiveInfoValue archiveInfo,
			final LeaveInfoValue leaveInfo, int rows) throws Exception {
		int pageNumbers = 0;
		String hqlStr = "SELECT liv FROM LeaveInfoValue liv,StaffInfoValue siv,ArchiveInfoValue aiv where liv.staffInfo.staffId= siv.staffId AND siv.archiveInfo.archiveId = aiv.archiveId";
		if (archiveInfo.getStaffName() != null) {
			hqlStr += " AND aiv.staffName =? ";
		}
		if (leaveInfo.getTitle() != null) {
			hqlStr += " AND liv.title = ?";
		}
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = -1;
				if (archiveInfo.getStaffName() != null) {
					index++;
					query.setString(index, archiveInfo.getStaffName());
				}
				if (leaveInfo.getTitle() != null) {
					index++;
					query.setString(index, leaveInfo.getTitle());
				}
				list = query.list();
				return list;
			}
		});
		this.numbers = list.size();
		if (numbers <= rows) {
			pageNumbers = 1;
		}
		if (numbers == 0) {
			pageNumbers = 0;
		} else {
			if (numbers % rows == 0) {
				pageNumbers = numbers / rows;
			} else {
				pageNumbers = numbers / rows + 1;
			}
		}
		return pageNumbers;
	}

	public int countTpagesByStaffId(final StaffInfoValue staffInfo,
			final int rows) throws Exception {
		int pageNumbers = 0;
		final String hql = "select liv from LeaveInfoValue liv where liv.staffInfo.staffId = ? and liv.states = ?";
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				query.setInteger(0, staffInfo.getStaffId());
				query.setInteger(1, staffInfo.getArchiveInfo().getPostInfo()
						.getSectionInfo().getSectionId());
				list = query.list();
				return list;
			}
		});
		this.numbers = list.size();
		if (numbers <= rows) {
			pageNumbers = 1;
		}
		if (numbers == 0) {
			pageNumbers = 0;
		} else {
			if (numbers % rows == 0) {
				pageNumbers = numbers / rows;
			} else {
				pageNumbers = numbers / rows + 1;
			}
		}
		return pageNumbers;
	}

	public int countTpagesByStaffId(final int staffId, final int states,
			int rows) throws Exception {
		int pageNumbers = 0;
		String hqlStr = "select liv from LeaveInfoValue liv where liv.staffInfo.staffId = ?";
		if (states != ILeaveInfoStates.UNDEFINED) {
			hqlStr += " and liv.states =?";
		}
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				query.setInteger(0, staffId);
				if (states != ILeaveInfoStates.UNDEFINED) {
					query.setInteger(1, states);
				}
				list = query.list();
				return list;
			}
		});
		this.numbers = list.size();
		if (numbers <= rows) {
			pageNumbers = 1;
		}
		if (numbers == 0) {
			pageNumbers = 0;
		} else {
			if (numbers % rows == 0) {
				pageNumbers = numbers / rows;
			} else {
				pageNumbers = numbers / rows + 1;
			}
		}
		return pageNumbers;
	}

	public List<LeaveInfoValue> queryLeaveInfoByStaffIdWithPage(
			final int staffId, final int states, final int rows,
			final int pageNo) throws Exception {
		String hqlStr = "select liv from LeaveInfoValue liv where liv.staffInfo.staffId = ?";
		if (states != ILeaveInfoStates.UNDEFINED) {
			hqlStr += " and liv.states = ?";
		}
		hqlStr += " order BY liv.startTime DESC";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				query.setInteger(0, staffId);
				if (states != ILeaveInfoStates.UNDEFINED) {
					query.setInteger(1, states);
				}
				query.setFirstResult(rows * (pageNo - 1));
				query.setMaxResults(rows);
				list = query.list();
				return list;
			}
		});
		return list;
	}

	public int getTotalNumber() throws Exception {
		return numbers;
	}

	public List<LeaveInfoValue> getList() {
		return list;
	}

	public void setList(List<LeaveInfoValue> list) {
		this.list = list;
	}

	public int getNumbers() {
		return numbers;
	}

	public void setNumbers(int numbers) {
		this.numbers = numbers;
	}

}
